using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using System.Web.UI.WebControls;
using Developex.DatabaseWrapper.Fields;

namespace Developex.DatabaseWrapper
{
	/// <summary>
	/// Helper class for creating sql query
	/// </summary>
	public class QueryBuilder
	{
		#region Fields and Properties

		/// <summary>
		/// Query Type
		/// </summary>
		public QueryBuilderType Type = QueryBuilderType.Select;

		/// <summary>
		/// From table name
		/// </summary>
		public string FromTable;

		/// <summary>
		/// From table alias
		/// </summary>
		public string TableAlias;

		/// <summary>
		/// Table name or alias if exists
		/// </summary>
		public string TableNameOrAlias
		{
			get { return TableAlias ?? FromTable; }
		}

		/// <summary>
		/// Select expressions
		/// </summary>
		public List<string> Select = new List<string>();

		/// <summary>
		/// for TOP keyword
		/// </summary>
		public string AfterSelect = "";

		/// <summary>
		/// Join expressions (with key word)
		/// </summary>
		public List<string> Join = new List<string>();

		/// <summary>
		/// Where expressions
		/// </summary>
		public List<string> Where = new List<string>();

		/// <summary>
		/// Order expressions
		/// </summary>
		public List<OrderInfo> Order = new List<OrderInfo>();

		/// <summary>
		/// Order expressions
		/// </summary>
		public List<string> Update = new List<string>();

		/// <summary>
		/// Parameters
		/// </summary>
		public List<IDataParameter> Parameters = new List<IDataParameter>();

		/// <summary>
		/// GroupBy string
		/// </summary>
		/// <remarks>GROUP BY keyword is not added automatically</remarks>
		public string GroupBy;

		private List<IFieldBase> ProcessedFields = new List<IFieldBase>();

		#endregion

		#region ToString

		/// <summary>
		/// Format query
		/// </summary>
		/// <returns></returns>
		public override string ToString()
		{
			// joins + auto-joins
			string joinExpression = string.Join(Environment.NewLine, Join.ToArray());

			string selectExpression = string.Join(", ", Select.ToArray());
			string updateExpression = string.Join(", ", Update.ToArray());
			if (!string.IsNullOrEmpty(joinExpression))
				joinExpression = " " + joinExpression;
			string whereExpression = "";
			if (Where.Count > 0)
			{
				whereExpression = string.Format(" WHERE ({0})", string.Join(")AND(", Where.ToArray()));
			}
			string orderExpression = OrderInfo.GetExpression(Order);
			switch (Type)
			{
				case QueryBuilderType.Select:
					return string.Format("SELECT {5} {0} FROM {1}{2}{3} {6} {4}", selectExpression, FromTable + (TableAlias == null ? "" : " " + TableAlias), joinExpression, whereExpression, orderExpression, AfterSelect, GroupBy ?? "");
				case QueryBuilderType.Delete:
					return string.Format("DELETE FROM {0}{1}", FromTable, whereExpression);
				case QueryBuilderType.Update:
					return string.Format("UPDATE {0} SET {1}{2}", FromTable, updateExpression, whereExpression);
			}
			return "";
		}

		#endregion

		#region Constructors

		/// <summary>
		/// Constructor
		/// </summary>
		/// <param name="fromTable"></param>
		public QueryBuilder(string fromTable)
		{
			FromTable = fromTable;
		}

		/// <summary>
		/// Constructor
		/// </summary>
		/// <param name="fromTable"></param>
		/// <param name="tableAlias"></param>
		public QueryBuilder(string fromTable, string tableAlias)
		{
			FromTable = fromTable;
			TableAlias = tableAlias;
		}

		/// <summary>
		/// Constructor
		/// </summary>
		/// <param name="mode"></param>
		/// <param name="fromTable"></param>
		public QueryBuilder(QueryBuilderType mode, string fromTable)
		{
			Type = mode;
			FromTable = fromTable;
		}

		/// <summary>
		/// Constructor
		/// </summary>
		/// <param name="mode"></param>
		/// <param name="fromTable"></param>
		/// <param name="tableAlias"></param>
		public QueryBuilder(QueryBuilderType mode, string fromTable, string tableAlias)
		{
			Type = mode;
			FromTable = fromTable;
			TableAlias = tableAlias;
		}

		#endregion

		#region Parameters

		///<summary>
		/// Add parameters and check unique of name
		///</summary>
		///<param name="parameters"></param>
		public void AddParameters(IEnumerable<IDataParameter> parameters)
		{
			foreach (IDataParameter parameter in parameters)
			{
				AddParameter(parameter);
			}
		}

		/// <summary>
		/// Add parameter and check unique of name
		/// </summary>
		/// <param name="parameter"></param>
		/// <returns>IDataParameter that was passed as [in] parameter</returns>
		public IDataParameter AddParameter(IDataParameter parameter)
		{
			foreach (IDataParameter par in Parameters)
			{
				if (par.ParameterName == parameter.ParameterName)
				{
					AddParameterWithNameCheck(parameter);
					return parameter;
				}
			}
			Parameters.Add(parameter);
			return parameter;
		}

		///<summary>
		/// Create and add parameter
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value"></param>
		///<returns>created parameter</returns>
		public IDataParameter CreateParameter(Connection conn, IField field, object value)
		{
			return AddParameter(conn.CreateParameter(field, value));
		}

		private void AddParameterWithNameCheck(IDataParameter parameter)
		{
			Dictionary<string, IDataParameter> parameters = new Dictionary<string, IDataParameter>();
			foreach (IDataParameter par in Parameters)
			{
				parameters.Add(par.ParameterName, par);
			}
			for (int i = 2 ; ; i++)
			{
				string name = parameter.ParameterName + i;
				if (!parameters.ContainsKey(name))
				{
					parameter.ParameterName = name;
					Parameters.Add(parameter);
					return;
				}
			}
		}

		/// <summary>
		/// Copy parameters
		/// </summary>
		/// <returns></returns>
		public List<IDataParameter> CopyParameters(Connection conn)
		{
			List<IDataParameter> copyParameters = new List<IDataParameter>();
			foreach (IDataParameter parameter in Parameters)
			{
				copyParameters.Add(conn.CreateParameter(parameter));
			}
			return copyParameters;
		}

		#endregion

		#region Prepare query builder for page query

		/// <summary>
		/// Prepare query builder for page query
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="pageIndex"></param>
		/// <param name="pageSize"></param>
		/// <param name="itemsCount"></param>
		/// <param name="primaryKeyFields"></param>
		public void PrepareForSelectQuery(Connection conn, int pageIndex, int pageSize, out int itemsCount, IField[] primaryKeyFields)
		{
			// check parameters
			if (pageSize < 1)
				throw new DatabaseException("Incorrect page size: " + pageSize, null, "QueryBuilder.PrepareForSelectQuery", GetType());
			if (pageIndex < 1)
				throw new DatabaseException("Incorrect page index: " + pageIndex, null, "QueryBuilder.PrepareForSelectQuery", GetType());

			switch (conn.ProviderType)
			{
				case ProviderType.MSSQL:
					PrepareForSelectQuery_MSSQL(conn, pageIndex, pageSize, out itemsCount, primaryKeyFields);
					break;
				case ProviderType.Access:
					PrepareForSelectQuery_Access(conn, pageIndex, pageSize, out itemsCount, primaryKeyFields);
					break;
				case ProviderType.Oracle:
					throw new NotImplementedException();
				case ProviderType.MySql:
					throw new NotImplementedException();
				default:
					throw new ArgumentOutOfRangeException();
			}
		}

		private void PrepareForSelectQuery_MSSQL(Connection conn, int pageIndex, int pageSize, out int itemsCount, IField[] primaryKeyFields)
		{
			List<string> selects = Select;
			Select = new List<string>();
			List<IDataParameter> parameters = CopyParameters(conn);

			// get items count
			Select.Add("COUNT(*)");
			List<OrderInfo> orders = Order;
			Order = new List<OrderInfo>();
			itemsCount = Convert.ToInt32(conn.ExecuteScalar(ToString(), Parameters.ToArray()));
			Order = orders;
			Parameters = CopyParameters(conn);

			if (primaryKeyFields != null)
			{
				// add ordering by primary key
				bool[] primaryOrderAdded = new bool[primaryKeyFields.Length];
				for (int j = 0; j < primaryOrderAdded.Length; j++)
				{
					primaryOrderAdded[j] = false;
				}
				foreach (OrderInfo orderInfo in Order)
				{
					bool continue_ = false;
					for (int j = 0; j < primaryOrderAdded.Length; j++)
					{
						if (primaryKeyFields[j].Name == orderInfo.Field)
						{
							primaryOrderAdded[j] = true;
							continue_ = true;
							break;
						}
					}
					if (continue_)
						continue;

					int pt = orderInfo.Field.IndexOf('.');
					if (pt != -1)
					{
						if (orderInfo.Field.Substring(0, pt).Trim('[', ']') == TableNameOrAlias)
						{
							string name = orderInfo.Field.Substring(pt + 1).Trim('[', ']');
							for (int j = 0; j < primaryOrderAdded.Length; j++)
							{
								if (primaryKeyFields[j].Name == name)
								{
									primaryOrderAdded[j] = true;
									break;
								}
							}
						}
					}
				}
				for (int j = 0; j < primaryOrderAdded.Length; j++)
				{
					if (!primaryOrderAdded[j])
					{
						Order.Add(new OrderInfo(string.Format("[{0}].[{1}]", TableNameOrAlias, primaryKeyFields[j].Name)));
					}
				}
			}

			// check if first page
			if (pageIndex == 1)
			{
				Select = selects;
				AfterSelect = "TOP " + pageSize;
				return;
			}

			// first query
			List<IDataParameter> orderParameters = new List<IDataParameter>();
			int i = 0;
			Select.Clear();
			foreach (OrderInfo orderInfo in Order)
			{
				IDataParameter parameter = conn.CreateParameter("par_" + i, DbType.Object, ParameterDirection.Output);
				orderParameters.Add(parameter);
				Parameters.Add(parameter);
				string name = orderInfo.Field;
				foreach (string selectExpression in selects)
				{
					string s = selectExpression.ToLower();
					int k = -1;
					if (s.IndexOf(" as ") != -1)
						k = s.IndexOf(" as ");
					else if (s.IndexOf("]as[") != -1)
						k = s.IndexOf("]as[");
					else if (s.IndexOf("]as ") != -1)
						k = s.IndexOf("]as ");
					else if (s.IndexOf(" as[") != -1)
						k = s.IndexOf(" as[");
					if (k != -1 && s.Substring(k + 3).Trim('[', ']', ' ', '\t', '\r', '\n') == name.Trim('[', ']', ' ', '\t', '\r', '\n').ToLower())
					{
						name = selectExpression.Substring(0, k + 1).Trim();
						orderInfo.Field = name;
						break;
					}
				}
				Select.Add(string.Format("{0} = {1}", parameter.ParameterName, name));
				i++;
			}
			AfterSelect = "TOP " + (pageIndex - 1) * pageSize;
			conn.ExecuteNonQuery(ToString(), Parameters.ToArray());

			// second query
			AfterSelect = "TOP " + pageSize;
			Parameters = parameters;
			Select = selects;
			parameters = new List<IDataParameter>();
			foreach (IDataParameter parameter in orderParameters)
			{
				if (parameter.Value != null && parameter.Value != DBNull.Value)
				{
					IDataParameter newParameter = conn.CreateParameter(parameter);
					newParameter.Direction = ParameterDirection.Input;
					if (!HelperFunctions.AutoDetectDbType(newParameter))
					{
						conn.OnDatabaseWarning(
							 new DatabaseWarning("Auto-detection of DbType failed - page query", "BaseRowList.LoadFromQueryBuilder", GetType()));
					}
					Parameters.Add(newParameter);
					parameters.Add(newParameter);
				}
				else
				{
					parameter.Value = null;
					parameters.Add(parameter);
				}
			}
			Where.Add(GetCompareWhereExpression(parameters, Order, 0));
		}

		private void PrepareForSelectQuery_Access(Connection conn, int pageIndex, int pageSize, out int itemsCount, IField[] primaryKeyFields)
		{
			List<string> selects = Select;
			Select = new List<string>();
			List<IDataParameter> parameters = CopyParameters(conn);

			// get items count
			Select.Add("COUNT(*)");
			List<OrderInfo> orders = Order;
			Order = new List<OrderInfo>();
			itemsCount = Convert.ToInt32(conn.ExecuteScalar(ToString(), Parameters.ToArray()));
			Order = orders;
			Parameters = CopyParameters(conn);

			if (primaryKeyFields != null)
			{
				// add ordering by primary key
				bool[] primaryOrderAdded = new bool[primaryKeyFields.Length];
				for (int j = 0; j < primaryOrderAdded.Length; j++)
				{
					primaryOrderAdded[j] = false;
				}
				foreach (OrderInfo orderInfo in Order)
				{
					bool continue_ = false;
					for (int j = 0; j < primaryOrderAdded.Length; j++)
					{
						if (primaryKeyFields[j].Name == orderInfo.Field)
						{
							primaryOrderAdded[j] = true;
							continue_ = true;
							break;
						}
					}
					if (continue_)
						continue;

					int pt = orderInfo.Field.IndexOf('.');
					if (pt != -1)
					{
						if (orderInfo.Field.Substring(0, pt).Trim('[', ']') == TableNameOrAlias)
						{
							string name = orderInfo.Field.Substring(pt + 1).Trim('[', ']');
							for (int j = 0; j < primaryOrderAdded.Length; j++)
							{
								if (primaryKeyFields[j].Name == name)
								{
									primaryOrderAdded[j] = true;
									break;
								}
							}
						}
					}
				}
				for (int j = 0; j < primaryOrderAdded.Length; j++)
				{
					if (!primaryOrderAdded[j])
					{
						Order.Add(new OrderInfo(string.Format("[{0}].[{1}]", TableNameOrAlias, primaryKeyFields[j].Name)));
					}
				}
			}

			// check if first page
			if (pageIndex == 1)
			{
				Select = selects;
				AfterSelect = "TOP " + pageSize;
				return;
			}

			// first query
			Select.Clear();
			string subSelect = "";
			int n = 0;
			foreach (OrderInfo orderInfo in Order)
			{
				string name = orderInfo.Field;
				foreach (string selectExpression in selects)
				{
					string s = selectExpression.ToLower();
					int k = -1;
					if (s.IndexOf(" as ") != -1)
						k = s.IndexOf(" as ");
					else if (s.IndexOf("]as[") != -1)
						k = s.IndexOf("]as[");
					else if (s.IndexOf("]as ") != -1)
						k = s.IndexOf("]as ");
					else if (s.IndexOf(" as[") != -1)
						k = s.IndexOf(" as[");
					if (k != -1 && s.Substring(k + 3).Trim('[', ']', ' ', '\t', '\r', '\n') == name.Trim('[', ']', ' ', '\t', '\r', '\n').ToLower())
					{
						name = selectExpression.Substring(0, k + 1).Trim();
						orderInfo.Field = name;
						break;
					}
				}
				if (subSelect.Length > 0)
					subSelect += ",";
				subSelect += "last(" + name + ")";
				n++;
			}
			Select.Add("*");
			AfterSelect = "TOP " + (pageIndex - 1) * pageSize;
			List<IDataParameter> orderParameters = new List<IDataParameter>();
			using (IDataReader reader = conn.ExecuteDataReader("SELECT " + subSelect + " FROM (" + ToString() + ")", Parameters.ToArray()))
			{
				reader.Read();
				for (int i = 0; i < n; i++)
				{
					IDataParameter parameter = conn.CreateParameter("par_" + i, reader[i]);
					orderParameters.Add(parameter);
				}
			}

			// second query
			AfterSelect = "TOP " + pageSize;
			Parameters = parameters;
			Select = selects;
			foreach (IDataParameter parameter in orderParameters)
			{
				if (parameter.Value != null && parameter.Value != DBNull.Value)
				{
					if (!HelperFunctions.AutoDetectDbType(parameter))
					{
						conn.OnDatabaseWarning(
							 new DatabaseWarning("Auto-detection of DbType failed - page query", "BaseRowList.LoadFromQueryBuilder", GetType()));
					}
					Parameters.Add(parameter);
				}
				else
				{
					parameter.Value = null;
				}
			}
			Where.Add(GetCompareWhereExpression(orderParameters, Order, 0));
		}


		// For page query
		private static string GetCompareWhereExpression(IList<IDataParameter> parameters, IList<OrderInfo> orderInfos, int index)
		{
			StringBuilder sb = new StringBuilder();
			if (parameters[index].Value == null)
			{
				sb.AppendFormat(orderInfos[index].SortAsc ? "NOT {0} IS NULL" : "1=2", orderInfos[index].Field);
				if (index < parameters.Count - 1)
				{
					sb.AppendFormat(" OR (({0} IS NULL) AND ({1}))", orderInfos[index].Field,
						 GetCompareWhereExpression(parameters, orderInfos, index + 1));
				}
			}
			else
			{
				sb.AppendFormat("{0} {2} {1}", orderInfos[index].Field, parameters[index].ParameterName,
					 orderInfos[index].SortAsc ? ">" : "<");
				if (index < parameters.Count - 1)
				{
					sb.AppendFormat(" OR (({0} = {1}) AND ({2}))", orderInfos[index].Field, parameters[index].ParameterName,
						 GetCompareWhereExpression(parameters, orderInfos, index + 1));
				}
			}
			return sb.ToString();
		}

		#endregion

		#region Adding WHERE conditions

		#region AddWhere

		///<summary>
		/// Get where condition
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value"></param>
		///<param name="par">null or already created parameter</param>
		///<returns>Where condition</returns>
		public string GetWhereCondition(Connection conn, IField field, object value, ref IDataParameter par)
		{
			CheckParentProcessed(field);
			if (value == null)
			{
				return field.GetSelectExpression() + " IS NULL";
			}
			if (par == null)
			{
				par = conn.CreateParameter(field, value);
				AddParameter(par);
			}
			return "NOT " + field.GetSelectExpression() + " IS NULL AND " + field.GetSelectExpression() + "=" + par.ParameterName;
		}

		///<summary>
		/// Get where condition
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value"></param>
		///<returns>Where condition</returns>
		public string GetWhereCondition(Connection conn, IField field, object value)
		{
			IDataParameter par = null;
			return GetWhereCondition(conn, field, value, ref par);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="field"></param>
		/// <param name="value"></param>
		/// <returns>created parameter if value != null</returns>
		public IDataParameter AddWhereCondition(Connection conn, IField field, object value)
		{
			IDataParameter par = null;
			Where.Add(GetWhereCondition(conn, field, value, ref par));
			return par;
		}

	   ///<summary>
		/// Get where condition
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value"></param>
		///<param name="condition"></param>
		///<param name="par">null or already created parameter</param>
		///<returns>Where condition</returns>
		public string GetWhereCondition(Connection conn, IField field, object value, string condition, ref IDataParameter par)
		{
			CheckParentProcessed(field);
			if (par == null)
			{
				par = conn.CreateParameter(field, value);
				AddParameter(par);
			}
			return field.GetSelectExpression() + condition + par.ParameterName;
		}

		///<summary>
		/// Get where condition
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value"></param>
		///<param name="condition"></param>
		///<returns>Where condition</returns>
		public string GetWhereCondition(Connection conn, IField field, object value, string condition)
		{
			IDataParameter par = null;
			return GetWhereCondition(conn, field, value, condition, ref par);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="field"></param>
		/// <param name="value"></param>
		/// <param name="condition"></param>
		/// <returns>created parameter</returns>
		public IDataParameter AddWhereCondition(Connection conn, IField field, object value, string condition)
		{
			IDataParameter par = null;
			Where.Add(GetWhereCondition(conn, field, value, condition, ref par));
			return par;
		}

		///<summary>
		/// Get where condition FormatString
		/// {0} - field name
		/// {1} - parameter name
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value"></param>
		///<param name="formatString">FormatString - {0} - field name {1} - parameter name</param>
		///<param name="par">null or already created parameter</param>
		///<returns>Where condition</returns>
		public string GetWhereConditionFormatString(Connection conn, IField field, object value, string formatString, ref IDataParameter par)
		{
			CheckParentProcessed(field);
			if (par == null)
			{
				par = conn.CreateParameter(field, value);
				AddParameter(par);
			}
			return string.Format(formatString, field.GetSelectExpression(), par.ParameterName);
		}

		///<summary>
		/// Get where condition FormatString
		/// {0} - field name
		/// {1} - parameter name
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value"></param>
		///<param name="formatString">FormatString - {0} - field name {1} - parameter name</param>
		///<returns>Where condition</returns>
		public string GetWhereConditionFormatString(Connection conn, IField field, object value, string formatString)
		{
			IDataParameter par = null;
			return GetWhereConditionFormatString(conn, field, value, formatString, ref par);
		}

		/// <summary>
		/// Add where condition FormatString
		/// {0} - field name
		/// {1} - parameter name
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="field"></param>
		/// <param name="value"></param>
		/// <param name="formatString">FormatString - {0} - field name {1} - parameter name</param>
		/// <returns></returns>
		public IDataParameter AddWhereConditionFormatString(Connection conn, IField field, object value, string formatString)
		{
			IDataParameter par = null;
			Where.Add(GetWhereConditionFormatString(conn, field, value, formatString, ref par));
			return par;
		}

		///<summary>
		/// Get where condition FormatString
		/// {0} - field name
		/// {1} - parameter name 1
		/// {2} - parameter name 2
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value1"></param>
		///<param name="value2"></param>
		/// <param name="formatString">FormatString - {0} - field name {1} - parameter name 1, {2} - parameter name 2</param>
		///<param name="par1">null or already created parameter(1)</param>
		///<param name="par2">null or already created parameter(2)</param>
		///<returns>Where condition</returns>
		public string GetWhereConditionFormatString(Connection conn, IField field, object value1, object value2, string formatString, ref IDataParameter par1, ref IDataParameter par2)
		{
			CheckParentProcessed(field);
			if (par1 == null)
			{
				par1 = conn.CreateParameter(field, value1);
				AddParameter(par1);
			}
			if (par2 == null)
			{
				par2 = conn.CreateParameter(field, value2);
				AddParameter(par2);
			}
			return string.Format(formatString, field.GetSelectExpression(), par1.ParameterName, par2.ParameterName);
		}

		///<summary>
		/// Get where condition FormatString
		/// {0} - field name
		/// {1} - parameter name 1
		/// {2} - parameter name 2
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="value1"></param>
		///<param name="value2"></param>
		/// <param name="formatString">FormatString - {0} - field name {1} - parameter name 1, {2} - parameter name 2</param>
		///<returns>Where condition</returns>
		public string GetWhereConditionFormatString(Connection conn, IField field, object value1, object value2, string formatString)
		{
			IDataParameter par1 = null, par2 = null;
			return GetWhereConditionFormatString(conn, field, value1, value2, formatString, ref par1, ref par2);
		}

		/// <summary>
		/// Add where condition FormatString
		/// {0} - field name
		/// {1} - parameter name 1
		/// {2} - parameter name 2
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="field"></param>
		/// <param name="value2"></param>
		/// <param name="formatString">FormatString - {0} - field name {1} - parameter name 1, {2} - parameter name 2</param>
		/// <param name="value1"></param>
		/// <returns></returns>
		public IDataParameter[] AddWhereConditionFormatString(Connection conn, IField field, object value1, object value2, string formatString)
		{
			IDataParameter par1 = null, par2 = null;
			Where.Add(GetWhereConditionFormatString(conn, field, value1, value2, formatString, ref par1, ref par2));
			return new IDataParameter[] { par1, par2 };
		}

		#endregion

		#region Where Or/And Conditions List - Simple

		///<summary>
		///</summary>
		///<param name="conditions"></param>
		///<returns></returns>
		public void AddWhereOrConditions(params string[] conditions)
		{
			string cc = GetWhereOrConditions(conditions);
			if (!string.IsNullOrEmpty(cc))
				Where.Add(cc);
		}

		///<summary>
		///</summary>
		///<param name="conditions"></param>
		///<returns></returns>
		public string GetWhereOrConditions(params string[] conditions)
		{
			if (conditions.Length == 0)
				return "";
			if (conditions.Length == 1)
				return (conditions[0]);
			return ("(" + string.Join(")OR(", conditions) + ")");
		}

		/// <summary>
		/// </summary>
		/// <param name="conditions"></param>
		public void AddWhereOrConditions(IEnumerable<string> conditions)
		{
			string cc = GetWhereOrConditions(conditions);
			if (!string.IsNullOrEmpty(cc))
				Where.Add(cc);
		}


		///<summary>
		///</summary>
		///<param name="conditions"></param>
		///<returns></returns>
		public string GetWhereAndConditions(params string[] conditions)
		{
			if (conditions.Length == 0)
				return "";
			if (conditions.Length == 1)
				return (conditions[0]);
			return ("(" + string.Join(")AND(", conditions) + ")");
		}

		/// <summary>
		/// </summary>
		/// <param name="conditions"></param>
		///<returns></returns>
		public string GetWhereOrConditions(IEnumerable<string> conditions)
		{
			if (conditions is List<string>)
				return GetWhereOrConditions(((List<string>)conditions).ToArray());
			List<string> cc = new List<string>(conditions);
			return GetWhereOrConditions(cc.ToArray());
		}

		///<summary>
		///</summary>
		///<param name="conditions"></param>
		public void AddWhereAndConditions(IEnumerable<string> conditions)
		{
			Where.Add(GetWhereAndConditions(conditions));
		}
		
		///<summary>
		///</summary>
		///<param name="conditions"></param>
		public void AddWhereAndConditions(params string[] conditions)
		{
			Where.Add(GetWhereAndConditions(conditions));
		}

		/// <summary>
		/// </summary>
		/// <param name="conditions"></param>
		///<returns></returns>
		public string GetWhereAndConditions(IEnumerable<string> conditions)
		{
			if (conditions is List<string>)
				return GetWhereAndConditions(((List<string>)conditions).ToArray());
			List<string> cc = new List<string>(conditions);
			return GetWhereAndConditions(cc.ToArray());
		}

		#endregion

		#region WhereOrConditions

		/// <summary>
		/// equal to one of given values
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="field"></param>
		/// <param name="values"></param>
		/// <returns>created parameter if value != null</returns>
		public void AddWhereOrConditionList<T>(Connection conn, IField field, IEnumerable<T> values)
		{
			string cc = GetWhereOrConditionList(conn, field, values);
			if (!string.IsNullOrEmpty(cc))
				Where.Add(cc);
		}

		/// <summary>
		/// equal to one of given values
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="field"></param>
		/// <param name="values"></param>
		/// <returns>created parameter if value != null</returns>
		public string GetWhereOrConditionList<T>(Connection conn, IField field, IEnumerable<T> values)
		{
			List<string> conditions = new List<string>();
			foreach (object value in values)
			{
				conditions.Add(GetWhereCondition(conn, field, value));
			}
			return GetWhereOrConditions(conditions);
		}

		///<summary>
		/// The same as AddWhereConditionList but use 'params'
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="values"></param>
		///<typeparam name="T"></typeparam>
		///<returns></returns>
		public void AddWhereOrConditionListP<T>(Connection conn, IField field, params T[] values)
		{
			AddWhereOrConditionList(conn, field, values);
		}

		///<summary>
		/// The same as AddWhereConditionList but use 'params'
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="values"></param>
		///<typeparam name="T"></typeparam>
		///<returns></returns>
		public string GetWhereOrConditionListP<T>(Connection conn, IField field, params T[] values)
		{
			return GetWhereOrConditionList(conn, field, values);
		}

		#endregion

		#region Text Search Conditions

		///<summary>
		/// Add where condition for text search 
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="snippet"></param>
		public IDataParameter AddSearchCondition(Connection conn, IField field, string snippet)
		{
			if (string.IsNullOrEmpty(snippet))
				return null;
			return AddWhereConditionFormatString(conn, field, "%" + snippet + "%", HelperFunctions.GetSearchFormatString(field));
		}

		///<summary>
		/// Add where condition for text search 
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="snippet"></param>
		///<param name="par"></param>
		public IDataParameter AddSearchCondition(Connection conn, IField field, string snippet, ref IDataParameter par)
		{
			if (par == null && string.IsNullOrEmpty(snippet))
				return null;
			Where.Add(GetSearchCondition(conn, field, snippet, ref par));
			return par;
		}

		///<summary>
		/// Get where condition for text search 
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="snippet"></param>
		///<returns></returns>
		public string GetSearchCondition(Connection conn, IField field, string snippet)
		{
			if (string.IsNullOrEmpty(snippet))
				return null;
			return GetWhereConditionFormatString(conn, field, "%" + snippet + "%", HelperFunctions.GetSearchFormatString(field));
		}

		///<summary>
		/// Get where condition for text search 
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="snippet"></param>
		///<param name="par"></param>
		///<returns></returns>
		public string GetSearchCondition(Connection conn, IField field, string snippet, ref IDataParameter par)
		{
			if (par == null && string.IsNullOrEmpty(snippet))
				return null;
			return GetWhereConditionFormatString(conn, field, "%" + snippet + "%", HelperFunctions.GetSearchFormatString(field), ref par);
		}

		///<summary>
		/// Get where condition for text search 
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		///<param name="par"></param>
		///<returns></returns>
		public string GetSearchCondition(Connection conn, IField field, IDataParameter par)
		{
			if (par == null)
				throw new InvalidOperationException("Parameter cannot be null");
			return GetSearchCondition(conn, field, null, ref par);
		}

		#endregion

		internal void AddWhereEqualCondition(Connection conn, IField field)
		{
			if (field.GetValue() == null)
				Where.Add("[" + field.Name + "] is NULL");
			else
			{
				IDataParameter parameter = conn.CreateParameter(field);
				Parameters.Add(parameter);
				Where.Add(string.Format("[{0}] = {1}", field.Name, parameter.ParameterName));
			}
		}

		#endregion

		#region AddOrder

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		public void AddOrder<T>(Field<T> field)
		{
			CheckParentProcessed(field);
			Order.Add(new OrderInfo(field.GetSelectExpression()));
		}

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		/// <param name="ascending"></param>
		public void AddOrder<T>(Field<T> field, bool ascending)
		{
			CheckParentProcessed(field);
			Order.Add(new OrderInfo(field.GetSelectExpression(), ascending));
		}

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		/// <param name="sort"></param>
		public void AddOrder<T>(Field<T> field, SortDirection sort)
		{
			CheckParentProcessed(field);
			Order.Add(new OrderInfo(field.GetSelectExpression(), sort));
		}

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		public void AddOrder(IField field)
		{
			CheckParentProcessed(field);
			Order.Add(new OrderInfo(field.GetSelectExpression()));
		}

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		/// <param name="ascending"></param>
		public void AddOrder(IField field, bool ascending)
		{
			CheckParentProcessed(field);
			Order.Add(new OrderInfo(field.GetSelectExpression(), ascending));
		}

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		/// <param name="sort"></param>
		public void AddOrder(IField field, SortDirection sort)
		{
			CheckParentProcessed(field);
			Order.Add(new OrderInfo(field.GetSelectExpression(), sort));
		}

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		public void AddOrder(string field)
		{
			Order.Add(new OrderInfo(field));
		}

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		/// <param name="ascending"></param>
		public void AddOrder(string field, bool ascending)
		{
			Order.Add(new OrderInfo(field, ascending));
		}

		/// <summary>
		/// Add Order
		/// </summary>
		/// <param name="field"></param>
		/// <param name="sort"></param>
		public void AddOrder(string field, SortDirection sort)
		{
			Order.Add(new OrderInfo(field, sort));
		}

		#endregion

		#region AddUpdate

		///<summary>
		///</summary>
		///<param name="conn"></param>
		///<param name="field"></param>
		public void AddUpdate(Connection conn, IField field)
		{
			object value = field.GetValue();
			if (value != null)
			{
				IDataParameter parameter = conn.CreateParameter(field);
				AddParameter(parameter);
				Update.Add("[" + field.Name + "]=" + parameter.ParameterName);
			}
			else Update.Add("[" + field.Name + "]=NULL");
		}

		#endregion

		#region Processed Fields

		///<summary>
		/// Is field already processed
		///</summary>
		///<param name="ff"></param>
		///<returns></returns>
		public bool IsProcessed(IFieldBase ff)
		{
			return ProcessedFields.Contains(ff);
		}

		///<summary>
		/// Mark field as processed for this query builder
		///</summary>
		///<param name="ff"></param>
		public void AddToProcessed(IFieldBase ff)
		{
			Debug.Assert(!IsProcessed(ff));
			ProcessedFields.Add(ff);
		}

		///<summary>
		/// Check if parent field(s) is processed
		///</summary>
		///<param name="field"></param>
		public void CheckParentProcessed(IFieldBase field)
		{
			IForeignObject ff = field.Parent.ParentField;
			if (ff == null || field.Parent._StandAloneMode)
				return;
			CheckParentProcessed(ff);
			if (!IsProcessed(ff))
			{
				ff.PrepareForSelectQuery(this);
				AddToProcessed(ff);
			}
		}

		#endregion

		#region Add Join

		///<summary>
		/// Add join expression
		///</summary>
		///<param name="foreignRelationField"></param>
		///<param name="primaryRelationField"></param>
		///<param name="joinType"></param>
		///<exception cref="ArgumentOutOfRangeException"></exception>
		public void AddJoin(IField foreignRelationField, IField primaryRelationField, JoinType joinType)
		{
			AddJoin(foreignRelationField, null, primaryRelationField, joinType);
		}

		///<summary>
		/// Add join expression
		///</summary>
		///<param name="foreignRelationField"></param>
		///<param name="alias"></param>
		///<param name="primaryRelationField"></param>
		///<param name="joinType"></param>
		///<exception cref="ArgumentOutOfRangeException"></exception>
		public void AddJoin(IField foreignRelationField, string alias, IField primaryRelationField, JoinType joinType)
		{
			// TODO: sql-provider depended
			string join;
			switch (joinType)
			{
				case JoinType.InnerJoin:
					join = "inner join";
					break;
				case JoinType.LeftJoin:
					join = "left join";
					break;
				default:
					throw new ArgumentOutOfRangeException("joinType");
			}
			if (string.IsNullOrEmpty(alias))
			{
				Join.Add(string.Format("{0} {1} on {2} = {3}", join, foreignRelationField.Parent.TableName_br,
					foreignRelationField.GetSelectExpression(), primaryRelationField.GetSelectExpression()));
			}
			else
			{
				if (!alias.StartsWith("[")) alias = "[" + alias + "]";
				Join.Add(string.Format("{0} {1} {2} on {2}.[{3}] = {4}", join, foreignRelationField.Parent.TableName_br, alias,
					foreignRelationField.Name, primaryRelationField.GetSelectExpression()));
			}
		}

		#endregion
	}
}
